﻿using Apps.Common;
using Apps.Models;
using Apps.Models.HuaChan;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Apps.DAL.YANX
{
    public partial class HUACHAN_Repository : DbContext<HUACHAN_PROJECT>
    {
        #region 化产项目管理

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public HUACHAN_PROJECT Info(string id)
        {
            return Db.Queryable<HUACHAN_PROJECT>().Where(m => m.ID == id).First();
        }

        /// <summary>
        /// 查询标题是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool ProjectAny(string name, string id = "")
        {
            if (string.IsNullOrEmpty(id))
                return Db.Queryable<HUACHAN_PROJECT>().Any(x => x.NAME == name);
            else
                return Db.Queryable<HUACHAN_PROJECT>().Any(x => x.NAME == name && x.ID != id);
        }

        public List<HUACHAN_PROJECT> GetList(int pageIndex, int pageSize, ref int totalCount, string sortFiled, string orderBy, string keyWord)
        {
            var exp = Expressionable.Create<HUACHAN_PROJECT>();
            exp.AndIF(!string.IsNullOrEmpty(keyWord), it => it.NAME.Contains(keyWord));
            return Db.Queryable<HUACHAN_PROJECT>().OrderBy(sortFiled + " " + orderBy).Where(exp.ToExpression()).ToPageList(pageIndex, pageSize, ref totalCount);
        }

        public List<HUACHAN_PROJECT> GetNopageList(string keyWord)
        {
            return Db.Queryable<HUACHAN_PROJECT>()
                .OrderBy(x => x.SORT)
                .Where(x => x.ISUSE == 1)
                .WhereIF(!string.IsNullOrEmpty(keyWord), it => it.NAME.Contains(keyWord)).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(HUACHAN_PROJECT model)
        {
            try
            {
                Db.BeginTran();
                Db.Insertable<HUACHAN_PROJECT_FIELDS>(model.fields).ExecuteCommand();
                Db.Insertable<HUACHAN_PROJECT>(model).ExecuteCommand();
                Db.CommitTran();
                return true;
            }
            catch
            {
                Db.RollbackTran();
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(HUACHAN_PROJECT model)
        {
            try
            {
                Db.BeginTran();
                //model.fields.ForEach(res =>
                //{

                //    if (Db.Queryable<HUACHAN_PROJECT_FIELDS>().Where(m => m.ID == res.ID).Any())
                //    {
                //        Db.Updateable<HUACHAN_PROJECT_FIELDS>(res).UpdateColumns(m => new
                //        {
                //            m.BASEVAL,
                //            m.COLUMNIDX,
                //            m.FIELDNAME,
                //            m.FIELDTYPE,
                //            m.KEEPPOINT,
                //            m.MAX,
                //            m.MIN,
                //            m.REQUIRED,
                //            m.SORT
                //        }).Where(m => m.ID == model.ID).ExecuteCommand();
                //    }
                //    else
                //    {
                //        Db.Insertable<HUACHAN_PROJECT_FIELDS>(res).ExecuteCommand();
                //    }

                //});
                Db.Deleteable<HUACHAN_PROJECT_FIELDS>().Where(m => m.PROJECTID == model.ID).ExecuteCommand();
                Db.Insertable<HUACHAN_PROJECT_FIELDS>(model.fields).ExecuteCommand();
                Db.Updateable<HUACHAN_PROJECT>(model).UpdateColumns(m => new
                {
                    m.NAME,
                    m.SORT,
                    m.ISUSE
                }).Where(m => m.ID == model.ID).ExecuteCommand();
                Db.CommitTran();
                return true;
            }
            catch
            {
                Db.RollbackTran();
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(string id)
        {
            try
            {
                Db.BeginTran();
                Db.Deleteable<HUACHAN_PROJECT_FIELDS>().Where(m => m.PROJECTID == id).ExecuteCommand();
                Db.Deleteable<HUACHAN_PROJECT>().Where(m => m.ID == id).ExecuteCommand();
                Db.CommitTran();
                return true;
            }
            catch
            {
                Db.RollbackTran();
                return false;
            }

        }

        #endregion

        /// <summary>
        /// 获取此化产项目包含的字段
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public List<HUACHAN_PROJECT_FIELDS> GetListDetail(string projectId)
        {
            return Db.Queryable<HUACHAN_PROJECT_FIELDS>().Where(m => m.PROJECTID == projectId).OrderBy(m => m.SORT).ToList();
        }

        /// <summary>
        /// 根据化产表单获取化产数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public List<HUACHAN_PROJECT_DATA> GetProjectData(int pageIndex, int pageSize, ref int totalCount, string sortFiled, string orderBy, string examine, string projectId, string startTime, string endTime)
        {
            var exp = Expressionable.Create<HUACHAN_PROJECT_DATA>();
            exp.And(it => it.PROJECTID == projectId);
            exp.AndIF(!string.IsNullOrEmpty(examine), it => it.EXAMINE == examine);
            exp.AndIF(!string.IsNullOrEmpty(startTime), it => it.ADDTIME >= $"{startTime} 00:00:01".ObjToDate());
            exp.AndIF(!string.IsNullOrEmpty(endTime), it => it.ADDTIME <= $"{endTime} 23:59:59".ObjToDate());
            return Db.Queryable<HUACHAN_PROJECT_DATA>().OrderBy(sortFiled + " " + orderBy).Where(exp.ToExpression()).ToPageList(pageIndex, pageSize, ref totalCount);
        }

        /// <summary>
        /// 根据化产表单获取化产数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public List<HUACHAN_PROJECT_DATA> GetProjectData(string sortFiled, string orderBy, string examine, string projectId, string startTime, string endTime)
        {
            var exp = Expressionable.Create<HUACHAN_PROJECT_DATA>();
            exp.And(it => it.PROJECTID == projectId);
            exp.AndIF(!string.IsNullOrEmpty(examine), it => it.EXAMINE == examine);
            exp.AndIF(!string.IsNullOrEmpty(startTime), it => it.ADDTIME >= $"{startTime} 00:00:01".ObjToDate());
            exp.AndIF(!string.IsNullOrEmpty(endTime), it => it.ADDTIME <= $"{endTime} 23:59:59".ObjToDate());
            return Db.Queryable<HUACHAN_PROJECT_DATA>().OrderBy(sortFiled + " " + orderBy).Where(exp.ToExpression()).ToList();
        }

        /// <summary>
        /// 添加化产表单数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public bool AddProjectData(HUACHAN_PROJECT_DATA model)
        {
            return Db.Insertable<HUACHAN_PROJECT_DATA>(model).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 编辑化产表单数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public bool UpdateProjectData(HUACHAN_PROJECT_DATA model)
        {
            return Db.Updateable<HUACHAN_PROJECT_DATA>(model).IgnoreColumns(m => new { m.ADDTIME, m.EXMINETIME, m.EXAMINE, m.PROJECTID }).Where(m => m.ID == model.ID).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 删除化产表单数据
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public bool DelProjectData(string dataId)
        {
            return Db.Deleteable<HUACHAN_PROJECT_DATA>().Where(m => m.ID == dataId).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 根据编号获取 数据
        /// </summary>
        /// <param name="dataId"></param>
        /// <returns></returns>
        public HUACHAN_PROJECT_DATA GetProjectData(string dataId)
        {
            return Db.Queryable<HUACHAN_PROJECT_DATA>().Where(m => m.ID == dataId).First();
        }

        /// <summary>
        /// 判断此项目是否存在记录
        /// </summary>
        /// <param name="dataId"></param>
        /// <returns></returns>
        public bool ThisProjectIsAnyData(string projetId)
        {
            return Db.Queryable<HUACHAN_PROJECT_DATA>().Where(m => m.PROJECTID == projetId).Any();
        }

        /// <summary>
        /// 根据编号获取 数据
        /// </summary>
        /// <param name="dataId"></param>
        /// <returns></returns>
        public DataTable GetProjectDataTable(string dataId)
        {
            return Db.Ado.GetDataTable("SELECT * FROM HUACHAN_PROJECT_DATA WHERE ID = @ID", new SugarParameter[] {
                new SugarParameter("@ID",dataId)
            });
        }

        /// <summary>
        /// 批量审核
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="examine"></param>
        /// <returns></returns>
        public bool ExamineProjectData(List<string> ids, string examine)
        {
            try
            {
                Db.BeginTran();
                ids.ForEach(res =>
                {
                    Db.Updateable<HUACHAN_PROJECT_DATA>(new HUACHAN_PROJECT_DATA
                    {
                        EXAMINE = examine,
                        EXMINETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).UpdateColumns(m => new { m.EXAMINE, m.EXMINETIME }).Where(m => m.ID == res).ExecuteCommand();
                });
                Db.CommitTran();
                return true;
            }
            catch
            {
                Db.RollbackTran();
                return false;
            }
        }

        #region 获取报表相关数据

        /// <summary>
        /// 根据条件查询所有数据
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="isExamine"></param>
        /// <returns></returns>
        public List<HUACHAN_PROJECT_DATA> GetAllData(string startDate, string endDate, string isExamine)
        {
            DateTime sDate = DateTime.Parse("1900-01-01 00:00:00");
            DateTime eDate = DateTime.Parse("1900-01-01 23:59:59");
            if (!string.IsNullOrEmpty(startDate))
                sDate = DateTime.Parse(startDate + " 00:00:00");
            if (!string.IsNullOrEmpty(endDate))
                eDate = DateTime.Parse(endDate + " 23:59:59");
            return Db.Queryable<HUACHAN_PROJECT_DATA>()
                .WhereIF(!string.IsNullOrEmpty(startDate), m => m.ADDTIME >= sDate)
                .WhereIF(!string.IsNullOrEmpty(startDate), m => m.ADDTIME <= eDate)
                .WhereIF(!string.IsNullOrEmpty(isExamine), m => m.EXAMINE == isExamine)
                .ToList();
        }

        /// <summary>
        /// 根据条件查询所有项目
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="isExamine"></param>
        /// <returns></returns>
        public List<HUACHAN_PROJECT> GetAllProject()
        {
          return   Db.Queryable<HUACHAN_PROJECT>().ToList();
        }

        /// <summary>
        /// 根据条件查询所有配置项
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="isExamine"></param>
        /// <returns></returns>
        public List<HUACHAN_PROJECT_FIELDS> GetAllFields()
        {
            return Db.Queryable<HUACHAN_PROJECT_FIELDS>().ToList();
        }

        #endregion

    }
}
